package br.com.cinesystem.persistence;

import br.com.cinesystem.entity.Cinema;
import br.com.cinesystem.entity.Endereco;
import com.mysql.jdbc.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class CinemaDao extends Dao{

    public CinemaDao() {

    }

    public void salvar(Cinema c) throws Exception{
        abrirConexao();

        stmt = con.prepareStatement("insert into cinema values(null,?,?,?,?,1)");
        stmt.setInt(1, c.getEndereco().getIdEndereco());
        stmt.setString(2, c.getRazaoSocial());
        stmt.setString(3, c.getCnpj());
        stmt.setString(4, c.getTelefone());
        stmt.execute();

        fecharConexao();
    }

    public boolean razaoExistente(String razao) throws Exception{
        abrirConexao();

        stmt = con.prepareStatement("select * from cinema where razaoSocial=?");
        stmt.setString(1, razao);

        rs = stmt.executeQuery();
        
        boolean b = rs.next();

        fecharConexao();
        return b;
    }

    public List<Cinema> buscarTodos()throws Exception{

        abrirConexao();

        StringBuilder sb = new StringBuilder();
        sb.append("select c.idCinema, c.id_Endereco, c.razaoSocial, c.cnpj, c.telefone, ");
        sb.append("e.logradouro, e.numero, e.complemento, e.cep, e.bairro, e.cidade, e.estado ");
        sb.append("from cinema c inner join endereco e ");
        sb.append("on c.id_Endereco = e.idEndereco ");
        sb.append("WHERE situacao=1");

        stmt = con.prepareStatement(sb.toString());        
        
        rs = stmt.executeQuery();

        List<Cinema> lista = new ArrayList<Cinema>();

        while (rs.next()){

            Cinema cinema = new Cinema();
            cinema.setEndereco(new Endereco());

            cinema.setIdCinema(rs.getInt("idCinema"));
            cinema.setRazaoSocial(rs.getString("razaoSocial"));
            cinema.setCnpj(rs.getString("cnpj"));
            cinema.setTelefone(rs.getString("telefone"));
            cinema.getEndereco().setIdEndereco(rs.getInt("id_Endereco"));
            cinema.getEndereco().setLogradouro(rs.getString("logradouro"));
            cinema.getEndereco().setNumero(rs.getString("numero"));
            cinema.getEndereco().setComplemento(rs.getString("complemento"));
            cinema.getEndereco().setCep(rs.getString("cep"));
            cinema.getEndereco().setBairro(rs.getString("bairro"));
            cinema.getEndereco().setCidade(rs.getString("cidade"));
            cinema.getEndereco().setEstado(rs.getString("estado"));

            lista.add(cinema);
        }
        
        fecharConexao();
        return lista;
    }
     public ResultSet buscarRelatorio()throws Exception{

        abrirConexao();

        StringBuilder sb = new StringBuilder();
        sb.append("select c.idCinema, c.id_Endereco, c.razaoSocial, c.cnpj, c.telefone, ");
        sb.append("e.logradouro, e.numero, e.complemento, e.cep, e.bairro, e.cidade, e.estado ");
        sb.append("from cinema c inner join endereco e ");
        sb.append("on c.id_Endereco = e.idEndereco ");
        sb.append("WHERE situacao=1");

        stmt = con.prepareStatement(sb.toString());

        rs = stmt.executeQuery();

        fecharConexao();
        return (ResultSet) rs;
    }

    public Cinema buscar(Cinema c) throws Exception{

        abrirConexao();

        StringBuilder sb = new StringBuilder();
        sb.append("select c.idCinema, c.id_Endereco, c.razaoSocial, c.cnpj, c.telefone, ");
        sb.append("e.logradouro, e.numero, e.complemento, e.cep, e.bairro, e.cidade, e.estado ");
        sb.append("from cinema c inner join endereco e ");
        sb.append("on c.id_Endereco = e.idEndereco ");
        sb.append("WHERE idCinema=?");

        stmt = con.prepareStatement(sb.toString());
        stmt.setInt(1, c.getIdCinema());

        rs = stmt.executeQuery();

        Cinema cinema = null;

        if(rs.next()){

            cinema = new Cinema();
            cinema.setEndereco(new Endereco());

            cinema.setIdCinema(rs.getInt("idCinema"));
            cinema.setRazaoSocial(rs.getString("razaoSocial"));
            cinema.setCnpj(rs.getString("cnpj"));
            cinema.setTelefone(rs.getString("telefone"));
            cinema.getEndereco().setIdEndereco(rs.getInt("id_Endereco"));
            cinema.getEndereco().setLogradouro(rs.getString("logradouro"));
            cinema.getEndereco().setNumero(rs.getString("numero"));
            cinema.getEndereco().setComplemento(rs.getString("complemento"));
            cinema.getEndereco().setCep(rs.getString("cep"));
            cinema.getEndereco().setBairro(rs.getString("bairro"));
            cinema.getEndereco().setCidade(rs.getString("cidade"));
            cinema.getEndereco().setEstado(rs.getString("estado"));

        }

        fecharConexao();
        return cinema;
    }

    public int buscarTotalRegistros() throws Exception{

        abrirConexao();

        stmt = con.prepareStatement("SELECT COUNT(*) as total FROM cinema");
        rs = stmt.executeQuery();
        rs.next();

        Integer num = new Integer(rs.getString("total"));

        fecharConexao();
        return num;

    }

    public void alterar(Cinema c) throws Exception{

        abrirConexao();

        stmt = con.prepareStatement("UPDATE cinema SET id_Endereco=?, razaoSocial=?, cnpj=?, " +
                "telefone=? WHERE idCinema=?");

        stmt.setInt(1, c.getEndereco().getIdEndereco());
        stmt.setString(2, c.getRazaoSocial());
        stmt.setString(3, c.getCnpj());
        stmt.setString(4, c.getTelefone());
        stmt.setInt(5, c.getIdCinema());

        stmt.execute();

        fecharConexao();

    }

    public void excluir(Cinema c) throws Exception{

        abrirConexao();

        stmt = con.prepareStatement("UPDATE cinema SET situacao=0 WHERE idCinema=?");

        stmt.setInt(1, c.getIdCinema());

        stmt.execute();

        fecharConexao();

    }

}
